import pandas as pd
from ToolUtils import Constant
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine,Column,Integer,String,Float,TIMESTAMP

engine_dev = create_engine(Constant.url_dev,encoding="utf-8",echo=True,max_overflow=5)
engine_localhost = create_engine(Constant.url_localhost,encoding="utf-8",echo=True,max_overflow=5)

# 一、数据查询
# 数据查询SQL
sql_dqsdk_user = """
SELECT * FROM `t_dqsdk_user`
limit 10
"""
sql_server_cost = """
SELECT * FROM `server_cost`
limit 100
"""

# 查询后数据类型为Dataframe
df_vip_info = pd.read_sql_query(sql_dqsdk_user, engine_dev)
df = pd.read_sql_query(sql_server_cost, engine_localhost)
print(df)

# 插入数据MySQL
df_vip_info.to_sql(name = 'table_name',con = engine_localhost,if_exists = 'append',index = False,index_label = False)

# 二、mysql操作(使用con.execute执行SQL语句)
# 对数据库进行SQL操作
with engine_localhost.connect() as con:
    con.execute('DROP TABLE IF EXISTS emp_backup;')
    con.execute('CREATE TABLE emp_backup LIKE table_name;')
    con.execute('DROP TABLE IF EXISTS table_name;')

# 将pandas dataframe转储到临时表中，然后将其与final table连接起来使用MySQL语句更新插入
'''
# DUMP TO TEMP TABLE (REPLACING EACH TIME) 
GetTimeSeriesLevels.to_sql(name='log_return_temp', con=mysql_engine, if_exists='replace', 
                           index=False)

# SQL UPDATE (USING TRANSACTION)
with engine.begin() as conn:     
    conn.execute("UPDATE market_price_data f" +
                 " INNER JOIN log_return_temp t" +
                 " ON f.Date = t.Date" +
                 " AND f.ticker = t.ticker" +
                 " SET f.log_return = t.log_return;")
'''

engine_dev.dispose()
engine_localhost.dispose()